<?php
/**
 * Created by PhpStorm.
 * User: dell
 * Date: 2019/1/29
 * Time: 14:12
 */
header("Content-type: text/html; charset=utf-8");
defined('BASEPATH') OR exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class Export extends CI_Controller
{
    public function index()
    {
        if (empty($this->input->get('month'))){
            $sql = "SELECT DISTINCT store,DATE_FORMAT(date_time, '%Y-%m-%d') as date_time FROM `item_effect` ORDER BY `date_time` DESC";
            $data['result'] = $this->db->query($sql)->result_array();

        }else{
            $sql = "SELECT DISTINCT store,DATE_FORMAT(date_time, '%Y-%m-%d') as date_time FROM `item_effect` WHERE DATE_FORMAT(date_time,'%Y-%m') = \"2019-01\" ORDER BY `date_time` DESC";
            $data['result'] = $this->db->query($sql)->result_array();
        }
        $this->load->view('export',$data);
    }

    public function download()
    {
        $date_time = $this->input->get('date');
        $shop = $this->input->get('store');
        $is_date=strtotime($date_time)?strtotime($date_time):time();

        if($is_date===false){
            exit('日期格式非法');
        }else{
            //明天
            $tomorrow = date('Y-m-d 00:00:00',$is_date+1*24*60*60);
            //当天
            $date_time = date('Y-m-d 00:00:00',$is_date);
            //昨天
            $yesterday = date('Y-m-d 00:00:00',$is_date-1*24*60*60);

            $spreadsheet = new Spreadsheet();

            $sheet = $spreadsheet->getActiveSheet()->setTitle("大盘数据");

            // 插入大盘数据 取当天
            $bigPlat = $this->db->select('DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time,visitor,add_purcha_peo_num,collect_peo_num')->where('date_time', $date_time)->get('big_plat')->result_array();
            array_unshift($bigPlat, ['日期','访客数','加购人数','收藏人数']);
            $sheet->fromArray($bigPlat);

            // 插入每日取数  取当天
            $this->db->select('DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time,pay_amt,visitor,custo_price,pay_conver_rate,pay_item_num,pay_num,
                pay_buy_num,collect_num,collect_buy_num,shop_collect_buy_num,jump_leave_rate,aver_leng_of_stay,
                 pageviews,per_capit_pageviews,desc_match_score,service_atti_score,logist_services_score,negat_commen_num,
                succe_refun_amt,add_purcha_num,add_purcha_peo_num,old_visitor,new_visitor,throu_train_pay,drill_exhib_pay,
                taobao_pay,pinxiaobao_pay,tot_pay,desc_same_aver,service_same_aver,logistic_same_aver');
            $dayDakeNum = $this->db->where('store', "{$shop}")->where('date_time', $date_time)->get('day_take_num')->result_array();
            array_unshift($dayDakeNum, ['统计日期','支付金额','访客数','客单价',
                '支付转化率','支付商品数','支付件数','支付买家数','商品收藏次数','商品收藏买家数',
                '店铺收藏买家数','跳失率','平均停留时长','浏览量','人均浏览量','描述相符评分','服务态度评分',
                '物流服务评分','负面评价数','成功退款金额','加购件数','加购人数','老访客数','新访客数','直通车花费',
                '钻展花费','淘宝客花费','品销宝花费','总花费','描述同行平均','服务同行平均','物流同行平均']);
            $spreadsheet->createSheet()->setTitle("每日取数")->fromArray($dayDakeNum);

            // 插入赤兔数据  取当天，昨天
            $chitu = $this->db->select('DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time,advisory_peo_num,cust_service_sale_pay,tomorrow_pay_succe_rate,wangwang_reply_rate,
            aver_response_time,slow_response_peo_num,long_receptio_peo_num')->where('store', "{$shop}")->where_in('date_time',[$date_time,$yesterday])->get('chitu_data')->result_array();
            array_unshift($chitu, ['日期','咨询人数','客服销售额','询单->次日付款成功率','旺旺回复率','平均响应时间','慢响应人数','长接待人数']);
            $spreadsheet->createSheet()->setTitle("赤兔数据")->fromArray($chitu);

            // 插入商品效果数据  取当天
            $this->db->select('belong_terminal,item_id,item_title,item_online_status,item_url,pageviews,visitor,
                aver_leng_of_stay,detai_page_bounc_rate,order_conver_rate,order_pay_conver_rate,
                pay_conver_rate,order_amt,order_item_num,order_buy_num,pay_amt,pay_item_num,add_purcha_num,
                visitor_aver_value,click_num,click_rate,exposur,collect_peo_num,search_lead_to_pay_buyer_num,
                custo_price,search_buy_rate,search_lead_visitor,pay_buy_num,afte_sale_succe_refun_amt,
                afte_sale_succe_refun_num,DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time,item_coding,year_season,positive_price,tag_price,tag_amt');
            $itemEffect = $this->db->where('store', "{$shop}")->where('date_time', $date_time)->get('item_effect')->result_array();
            array_unshift($itemEffect, ['所属终端','商品id','商品标题','商品在线状态','商品链接','浏览量',
                '访客数','平均停留时长','详情页跳出率','下单转化率','下单支付转化率','支付转化率','下单金额',
                '下单商品件数','下单买家数','支付金额','支付商品件数','加购件数','访客平均价值','点击次数',
                '点击率','曝光量','收藏人数','搜索引导支付买家数','客单价','搜索支付转化率','搜索引导访客数',
                '支付买家数','售中售后成功退款金额','售中售后成功退款笔数','日期','商品编码','年份季节','正特价',
                '吊牌价','吊牌额']);
            $spreadsheet->createSheet()->setTitle("商品效果")->fromArray($itemEffect);

            // 插入页面库存数据  取明天
            $itemStock = $this->db->select('item_id,item_status,item_url,year_season,item_title,item_price,item_discoun_price,
            item_stock,mercha_coding,DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time,positive_price')->where('store', "{$shop}")->where('date_time', $tomorrow)->get('item_stock')->result_array();
            array_unshift($itemStock, ['宝贝ID','宝贝状态','宝贝链接','年份季节','标题','价格','折扣价','库存','商家编码','日期','正特价']);
            $spreadsheet->createSheet()->setTitle("页面库存")->fromArray($itemStock);

            // 插入匹配数据
            $matchData = $this->db->select('item_id,item_coding,year_season,tag_price')->where('store', "{$shop}")->get('match_data')->result_array();
            array_unshift($matchData, ['商品id','商品编码','年份季节','吊牌价']);
            $spreadsheet->createSheet()->setTitle("匹配数据")->fromArray($matchData);

            // 插入流量数据  取当天
            $flowData = $this->db->select('flow_source,source_detail,visitors,visitors_change,order_amt,order_amt_change,order_buy,order_buy_change,order_conversion,order_conversion_change,pay_amt,pay_amt_change,
                pay_buy,pay_buy_change,pay_conversion,pay_conversion_change,customer_price,customer_price_change,uv_value,uv_value_change,attention_store_buy,attention_store_buy_change,collect_item_buy,
                collect_item_buy_change,plus_num,plus_num_change,new_visitors,new_visitors_change')->where('store', "{$shop}")->where('date_time', $date_time)->get('flow_data')->result_array();
            array_unshift($flowData, ['流量来源','来源明细','访客数','访客数变化','下单金额','下单金额变化','下单买家数','下单买家数变化','下单转化率','下单转化率变化','支付金额','支付金额变化','支付买家数',
                '支付买家数变化','支付转化率','支付转化率变化','客单价','客单价变化','UV价值','uv价值变化','关注店铺买家数','关注店铺买家数变化','收藏商品买家数','收藏商品买家数变化','加购人数','加购人数变化','新访客',
                '新访客变化']);
            $spreadsheet->createSheet()->setTitle("流量数据")->fromArray($flowData);

            $writer = new Xlsx($spreadsheet);

            $filename = "大盘基础数据".date('Y-m-d');

            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
            header('Cache-Control: max-age=0');

            $writer->save('php://output'); //下载文件
        }



    }

}
